package org.micode.common.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.micode.common.exception.DataException;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicReference;

/**
 * Excel导入导出工具类
 */
@Slf4j
public class ExcelUtils {

    /**
     * 判断excel文件后缀名，生成不同的workbook
     */
    private static Workbook createWorkbook(InputStream is, String excelFileName) throws IOException {
        if (excelFileName.endsWith(".xls")) {
            return new HSSFWorkbook(is);
        } else if (excelFileName.endsWith(".xlsx")) {
            return new XSSFWorkbook(is);
        }
        return null;
    }

    /**
     * 根据sheet索引号获取对应的sheet
     */
    private static Sheet getSheet(Workbook workbook, int sheetIndex) {
        return workbook.getSheetAt(sheetIndex);
    }

    /**
     * 将sheet中的数据保存到list中，
     * 1、调用此方法时，vo的属性个数必须和excel文件每行数据的列数相同且一一对应，vo的所有属性都为String
     * 2、在action调用此方法时，需声明 private File excelFile;上传的文件 private String excelFileName;原始文件的文件名
     * 3、页面的file控件name需对应File的文件名
     */
    @SuppressWarnings("deprecation")
    public static List<Object> importDataFromExcel(Object vo, InputStream is, String excelFileName) {
        List<Object> list = new ArrayList<>();
        try {
            // 创建工作簿
            Workbook workbook = createWorkbook(is, excelFileName);
            // 创建工作表sheet
            Sheet sheet = getSheet(Objects.requireNonNull(workbook), 0);
            // 获取sheet中数据的行数
            int rows = sheet.getPhysicalNumberOfRows();
            // 获取表头单元格个数
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            // 利用反射，给JavaBean的属性进行赋值
            Field[] fields = vo.getClass().getDeclaredFields();
            for (int i = 1; i < rows; i++) {// 第一行为标题栏，从第二行开始取数据
                Row row = sheet.getRow(i);
                int index = 0;
                while (index < cells) {
                    Cell cell = row.getCell(index);
                    if (null == cell) {
                        cell = row.createCell(index);
                    }
                    cell.setCellType(CellType.STRING);
                    String value = null == cell.getStringCellValue() ? "" : cell.getStringCellValue();

                    Field field = fields[index];
                    String fieldName = field.getName();
                    String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Method setMethod = vo.getClass().getMethod(methodName, String.class);
                    setMethod.invoke(vo, value);
                    index++;
                }
                if (isHasValues(vo)) {// 判断对象属性是否有值
                    list.add(vo);
                    vo = vo.getClass().getConstructor(new Class[]{}).newInstance();// 重新创建一个vo对象
                }

            }
        } catch (Exception e) {
            log.error("Handling excel file failed", e);
        } finally {
            try {
                is.close();// 关闭流
            } catch (Exception e2) {
                log.error("Closing excel stream failed", e2);
            }
        }

        return list;
    }

    /**
     * 将sheet中的数据保存到list中，
     */
    public static List<Map<String, String>> importDataFromExcel(InputStream is, String excelFileName) {
        List<Map<String, String>> out = new ArrayList<>();
        try {
            // 创建工作簿
            Workbook workbook = createWorkbook(is, excelFileName);
            // 创建工作表sheet
            Sheet sheet = getSheet(Objects.requireNonNull(workbook), 0);
            // 获取sheet中数据的行数
            int rows = sheet.getPhysicalNumberOfRows();

            // 获取表头单元格个数
            Row rowTitle = sheet.getRow(0);
            int cells = rowTitle.getPhysicalNumberOfCells();

            Map<Integer, String> titleMap = new HashMap<>();

            AtomicReference<Integer> idx = new AtomicReference<>(0);
            rowTitle.cellIterator().forEachRemaining(cell -> {
                String value = cell.getStringCellValue();
                if (StringExtUtils.isAnyEmpty(value)) {
                    throw new DataException("Excel file have invalid data (title row)!");
                }
                titleMap.put(idx.get(), value);
                idx.set(idx.get() + 1);
            });

            if (titleMap.size() < cells) {
                throw new DataException("Excel file title row have double item!");
            }

            for (int i = 1; i < rows; i++) {
                // 第一行为标题栏，从第二行开始取数据
                Row row = sheet.getRow(i);
                Map<String, String> valueMap = new HashMap<>();
                int index = 0;
                while (index < cells) {
                    Cell cell = row.getCell(index);
                    if (null == cell) {
                        cell = row.createCell(index);
                    }
                    cell.setCellType(CellType.STRING);
                    String value = null == cell.getStringCellValue() ? "" : cell.getStringCellValue();

                    valueMap.put(titleMap.get(index), value);
                    index++;
                }
                out.add(valueMap);
            }
        } catch (Exception e) {
            log.error("Handling excel file failed", e);
        } finally {
            try {
                is.close();// 关闭流
            } catch (Exception e2) {
                log.error("Closing excel stream failed", e2);
            }
        }

        return out;
    }

    /**
     * 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true
     */
    private static boolean isHasValues(Object object) {
        Field[] fields = object.getClass().getDeclaredFields();
        boolean flag = false;
        for (Field field : fields) {
            String fieldName = field.getName();
            String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
            Method getMethod;
            try {
                getMethod = object.getClass().getMethod(methodName);
                Object obj = getMethod.invoke(object);

                if (null != obj && !"".equals(obj)) {
                    flag = true;
                    break;
                }
            } catch (Exception e) {
                log.error("Handling excel object failed", e);
            }

        }
        return flag;

    }

    public static <T> void exportDataToExcel(List<T> list,
                                             String[] headers,
                                             String title,
                                             String excelFileName) {
        exportDataToExcel(list, headers, title, excelFileName, null);
    }

    public static <T> void exportDataToExcel(List<T> list,
                                             String[] headers,
                                             String title,
                                             String excelFileName,
                                             String[] filterFields) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            exportExcel(list, headers, title, workbook, filterFields);

            File file = new File(excelFileName);
            try {
                workbook.write(file);
            } catch (Exception e) {
                log.error("Writing to excel workbook failed", e);
            }
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                log.error("Closing excel workbook failed", e);
            }
        }
    }

    public static <T> void exportDataToExcel(List<T> list,
                                             String[] headers,
                                             String title,
                                             OutputStream os) {
        exportDataToExcel(list, headers, title, os, null);
    }

    public static <T> void exportDataToExcel(List<T> list,
                                             String[] headers,
                                             String title,
                                             OutputStream os,
                                             String[] filterFields) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            exportExcel(list, headers, title, workbook, filterFields);

            try {
                workbook.write(os);
            } catch (Exception e) {
                log.error("Writing to excel workbook failed", e);
            } finally {
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    log.error("Flush excel workbook failed", e);
                }
            }
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                log.error("Closing excel workbook failed", e);
            }
        }
    }

    private static <T> void exportExcel(List<T> list,
                                        String[] headers,
                                        String title,
                                        HSSFWorkbook workbook,
                                        String[] filterFields) {
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽15个字节
        sheet.setDefaultColumnWidth(15);

        // 生成表格标题
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 300);
        HSSFCell cell;

        String[] fieldnames = buildFieldNames(list, filterFields);

        if (null == fieldnames) {
            throw new DataException("The list value invalid!");
        }

        if (null == headers) {
            headers = fieldnames;
        }

        for (int i = 0; i < headers.length; i++) {
            cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        // 将数据放入sheet中
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            T t = list.get(i);
            // 利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值
            try {
                for (int j = 0; j < fieldnames.length; j++) {
                    cell = row.createCell(j);
                    String fieldName = fieldnames[j];
                    String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Method getMethod = t.getClass().getMethod(methodName);
                    Object value = getMethod.invoke(t);
                    log.trace("导出信息：{} {} {}", fieldName, methodName, value);
                    if (null == value) {
                        value = "";
                    }
                    cell.setCellValue(value.toString());
                }
            } catch (Exception e) {
                log.error("Writing to excel cell failed", e);
            }
        }
    }

    private static <T> String[] buildFieldNames(List<T> list,
                                                String[] filterFields) {
        if (null == list) {
            return null;
        }

        T t = list.get(0);
        // 利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值
        Field[] fields = t.getClass().getDeclaredFields();
        Field[] parentFields = t.getClass().getSuperclass().getDeclaredFields();

        List<Field> fieldList = Arrays.asList(fields);
        List<Field> parentFieldList = Arrays.asList(fields);
        if (null != filterFields && filterFields.length > 0) {
            fieldList = filterTheFiled(fields, filterFields);
            parentFieldList = filterTheFiled(parentFields, filterFields);
        }

        int selfNum = fieldList.size();
        String[] out = new String[selfNum + parentFieldList.size()];
        for (int i = 0; i < selfNum; i++) {
            out[i] = fieldList.get(i).getName();
        }

        for (int i = 0; i < parentFieldList.size(); i++) {
            out[i + selfNum] = parentFieldList.get(i).getName();
        }

        return out;
    }

    private static List<Field> filterTheFiled(Field[] theFields, String[] filterFields) {
        List<String> filters = Arrays.asList(filterFields);
        List<Field> out = new ArrayList<>();
        for (Field field : theFields) {
            if (!filters.contains(field.getName())) {
                out.add(field);
            }
        }
        return out;
    }

}
